package com.example.demo.tool;

import java.sql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.postgresql.core.BaseConnection;

import com.zaxxer.hikari.HikariDataSource;

public class DBConnection {
	
    private String url;
	
	private String username;
	
	private String password;
	
	private String driverClassName;
	
	private HikariDataSource dataSource;
	
	private Connection connection;
	
	private Pattern urlPattern = Pattern.compile("jdbc\\s*:\\s*(?<type>[a-z]+)\\s*:\\s*//"
			+ "\\s*?(?<host>[a-zA-Z0-9-//.]+)\\s*?(:\\s*?(?<port>[0-9]+))?+\\s*?/"
			+ "\\s*?(?<database>[a-zA-Z0-9_]+)?+\\s*?(\\?(?<params>(\\s|\\S)+))?");

	//关闭通过JDBC连接数据库的指定用户所在的数据库连接
	private String closeConnectSQL = "SELECT pg_terminate_backend(\r\n" +
			" (\r\n" +
			" SELECT pid FROM\r\n" +
			" (\r\n" +
			" SELECT\r\n" +
			" pgsa.pid AS pid,\r\n" +
			" pgsa.datname AS datname,\r\n" +
			" pgsa.usename AS usename,\r\n" +
			" pgsa.client_addr client_addr,\r\n" +
			" pgsa.application_name AS application_name,\r\n"+
			" pgsa.STATE AS STATE,\r\n" +
			" pgsa.backend_start AS backend_start,\r\n" +
			" pgsa.xact_start AS xact_start,\r\n" +
			" EXTRACT ( epoch FROM ( Now( ) - pgsa.xact_start) ) AS xact_stay,\r\n" +
			" pgsa.query_start AS query_start,\r\n" +
			" EXTRACT ( epoch FROM ( Now( ) -	pgsa.query_start ) ) AS query_stay,\r\n" +
			" pgsa.query AS query \r\n" +
			" FROM\r\n" +
			" pg_stat_activity AS pgsa \r\n" +
			" WHERE\r\n" +
			" pgsa.STATE != 'idle' AND pgsa.STATE != 'idle in transaction' \r\n" +
			" AND pgsa.STATE != 'idle in transaction (aborted)' \r\n" +
			" AND usename= ? AND application_name='PostgreSQL JDBC Driver'\r\n" +
			" ) idleconnections \r\n" +
			" ORDER BY query_stay DESC LIMIT 0,1\r\n" +
			" )\r\n" +
			")";


	//取消当前执行的全部SQL语句，方式是关闭SQL语句所在的连接并建立新连接
	public boolean cancelAll() throws SQLException {
		Connection newConnection = this.dataSource.getConnection();
		PreparedStatement preparedStatement = 	newConnection.prepareStatement(closeConnectSQL);
		preparedStatement.setString(1, this.username); //向SQL语句传入用户名，用于查找该用户当前执行较慢的SQL语句所在线程
		preparedStatement.execute(); //执行SQL语句，通过杀掉线程的方式实现关闭连接效果
		ResultSet rs = preparedStatement.getResultSet();
		rs.next();
		boolean closeResult = rs.getBoolean(1); //获取执行结果，closeResult=true表示关闭成功，=false表示当前没有执行SQL语句或线程无法终止
		preparedStatement.close();
		if (closeResult) {
			this.connection = newConnection;
			return true;
		} else {
			newConnection.close();
			return false;
		}
	}


	public DBConnection(DBDatasource dbDatasource) {
		this.url = dbDatasource.getUrl();
		this.username = dbDatasource.getUsername();
		this.password = dbDatasource.getPassword();
		this.driverClassName = dbDatasource.getDriverClassName();
		this.dataSource = null;
		this.connection = null;
	}
	
	public void setDatabase(String dbName) {
		//this.url = this.url.replace("/systemmanager", "/"+dbName);
		//采用正则表达式获取配置文件中数据库名称和后续配置参数，将其替换为传入数据库名称
		Matcher urlMatcher = urlPattern.matcher(this.url);
		urlMatcher.matches();
		String replaceString = this.url.substring(urlMatcher.start("database"));
		this.url = this.url.replace(replaceString, dbName);
	}
	
	public void setUsername(String username) {
		username = username.replace("\"", "");
		this.username = username;
	}
	
	public void setPassword(String password) {
		this.password = password;
	}
	
	public void setIP(String IP) {
		//this.url = this.url.replace("//*:", "//"+IP+":");
		Matcher urlMatcher = urlPattern.matcher(this.url);
		urlMatcher.matches();
		String urlIP = urlMatcher.group("host");
		this.url = this.url.replace(urlIP, IP);
	}
	
	public void setPort(int port) {
		//this.url = this.url.replace(":*/", ":"+port+"/");
		Matcher urlMatcher = urlPattern.matcher(this.url);
		urlMatcher.matches();
		String urlPort = urlMatcher.group("port");
		//原url中存在端口，直接替换；不存在端口，将端口追加到IP后
		if (urlPort != null)
			this.url = this.url.replaceFirst(urlPort, String.valueOf(port));
		else {
			String urlIP = urlMatcher.group("host");
			this.url = this.url.replace(urlIP, urlIP + ":" + port);
		}
	}
	
	private void getDataSource() throws SQLException {
		HikariDataSource dataSource = new HikariDataSource();
		
		dataSource.setJdbcUrl(this.url);
		dataSource.setUsername(this.username);
		dataSource.setPassword(this.password);
		dataSource.setDriverClassName(this.driverClassName);
		dataSource.setMaximumPoolSize(5);
        dataSource.setMinimumIdle(0);
        dataSource.setConnectionTimeout(30000);
		
		this.dataSource = dataSource;
	}
	
	public Connection getConnect() throws SQLException {
		if (this.dataSource == null)
		{
			getDataSource();
		}
//		if (this.connection == null || !this.connection.isValid(500))
		if (this.connection == null || this.connection.isClosed())
		{
			this.connection = this.dataSource.getConnection();
		}
		return this.connection;
	}
	
	public void close() throws SQLException {
//		if (connection.isValid(500) || !connection.isClosed())
		if (!connection.isClosed())
		    this.connection.close();
		
		this.dataSource.close();
	}
	
	public BaseConnection getPgConnect() throws SQLException, ClassNotFoundException {
		Class.forName(this.driverClassName);
		Connection connection = DriverManager.getConnection(this.url, this.username, this.password);
		return (BaseConnection)connection;
	}
	
	

}
